//
//  AZSQLiteManager.swift
//  FMDB
//
//  Created by Albert Zhu on 16/10/28.
//  Copyright © 2016年 Albert Zhu. All rights reserved.
//

import Foundation
import FMDB

/// 数据库管理器
/*
 1.创建并打开数据库队列
 2.建表
 3.增删改查
 */
private let maxDBCache: TimeInterval = -5 * 24 * 60 * 60

class AZSQLiteManager {
    // 数据库单例
    static let shared = AZSQLiteManager()
    let queue: FMDatabaseQueue
    
    private init() {
        let dbName = "status.db"
        let path = dbName.az_appendDocumentDir()
        debugPrint(path)
        
        // 创建数据库队列，同时创建或者打开数据库
        queue = FMDatabaseQueue(path: path)

        // 打开数据库
        createTable()
    
        NotificationCenter.default.addObserver(self, selector: #selector(clearDBCache), name: NSNotification.Name.UIApplicationDidEnterBackground, object: nil)
    }
    
    deinit {
        NotificationCenter.default.removeObserver(self)
    }
    
    /// 清理数据库缓存
    /// SQLite的数据库删除了数据，数据库的大小不会变小
    @objc func clearDBCache() {
        
        let dateString = Date.az_dateString(delta: maxDBCache)
        
        let sql = "DELETE FROM T_status WHERE createtime < ?;"
        
        queue.inDatabase { (db) in
            if db?.executeUpdate(sql, withArgumentsIn: [dateString]) == true {
                debugPrint("删除了\(db?.changes())条记录")
            }
        }
    }
}

// MARK: - 微博数据操作
extension AZSQLiteManager {
    /// 新增或者修改微博数据，微博数据刷新，可能会出现重叠
    ///
    /// - parameter userId: 当前登录用户的id
    /// - parameter array:  从网络获取json字典中的数组
    func updateStatus(userId: String, array: [[String:Any]]) {
        // 1.准备SQL
        let sql = "INSERT OR REPLACE INTO T_status (statusid, userid, status) VALUES (?, ?, ?);"
        // 2.执行SQL
        queue.inTransaction { (db, rollBack) in
            for dic in array {
                
//                guard let dic = dict as? [String:Any] else {continue}
                
                // 从字典获取微博代号，将字典转为二进制数据
                guard let statusId = dic["idstr"] as? String,
                    let jsonData = try? JSONSerialization.data(withJSONObject: dic, options: [])
                    else {continue}
                
                if db?.executeUpdate(sql, withArgumentsIn: [statusId, userId, jsonData]) == false {
                    // 执行失败进行回滚
                    rollBack?.pointee = true
                    
                    break
                }
            }
        }
    }
}

// MARK: - 创建数据表和其他私有方法
extension AZSQLiteManager {
    /// 从数据库加载微博数据
    ///
    /// - parameter statusId: 用户id
    /// - parameter since_id: 返回比id大的微博
    /// - parameter max_id:   返回比id小的微博
    ///
    /// - returns: 微博的字典数组，将数据库中status对应的二进制数据反序列化
    func loadStatus(userId: String, since_id: Int64, max_id: Int64) -> [[String:Any]] {
        // 准备SQL
        var sql = "SELECT statusid, userid, status FROM T_status\n"
        sql += "WHERE userid = \(userId)\n"
        if since_id > 0 {
            sql += "AND statusid > \(since_id)\n"
        }else if max_id > 0 {
            sql += "AND statusid < \(max_id)\n"
        }
        sql += "ORDER BY statusid DESC LIMIT 20;"
        
        debugPrint(sql)
        
        // 执行SQL
        let array = exeRecordSet(sql: sql)
        
        // 遍历数组，将二进制数据反序列化为字典
        var result = [[String:Any]]()
        
        for dic in array {
            // 反序列化
            guard let jsonData = dic["status"] as? Data,
            let json = try? JSONSerialization.jsonObject(with: jsonData, options: []) as? [String:Any] else {continue}
        
            result.append(json ?? [:])
        }
        
        return result
    }
    
    /// 创建数据表
    func createTable() {
        // 准备sql
        guard let path = Bundle.main.path(forResource: "status.sql", ofType: nil),
            let sql = try? String(contentsOfFile: path) else {return}
        // 执行sql
        debugPrint(sql)
        
        queue.inDatabase { (db) in
            if db?.executeStatements(sql) == true {
                debugPrint("创表成功")
            }else{
                debugPrint("创表失败")
            }
        }
        debugPrint("over")
    }
    
    /// 执行一个SQL，返回字典数组
    ///
    /// - parameter sql: sql
    ///
    /// - returns: 字典数组
    func exeRecordSet(sql: String) -> [[String:Any]] {
        
        var result = [[String:Any]]()
        
        queue.inDatabase { (db) in
            guard let rs = db?.executeQuery(sql, withArgumentsIn: []) else {return}
            
            while rs.next() {
                let colCount = rs.columnCount()
                
                for col in 0..<colCount {
                    guard let name = rs.columnName(for: col),
                        let value = rs.object(forColumnIndex: col) else {continue}
                    
                    result.append([name:value])
                }
            }
        }
        return result
    }
}
